import copy
import os

from itsdangerous import TimedJSONWebSignatureSerializer as Serializer, SignatureExpired, BadSignature
from sqlalchemy import Column, JSON, String, TIMESTAMP, text, Index, TEXT, ForeignKey
from sqlalchemy.dialects.mysql import INTEGER, TINYINT
# C:\Users\xuzhu\.virtualenvs\flaskserver-PKgKgxc4\Scripts>.\sqlacodegen.exe --outfile C:\\Users\\xuzhu\\Desktop\\mod.py mysql+pymysql://root:vipdatacenter@192.168.31.207:3306/data_warehouse?charset=utf8
from werkzeug.security import generate_password_hash, check_password_hash

from apps.core.database import db


class LogSys(db.Model):
    __tablename__ = 'log_sys'

    id = Column(INTEGER(11), primary_key=True)
    log_json = Column(JSON, nullable=False, comment='json格式的日志信息')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class LogUser(db.Model):
    __tablename__ = 'log_user'

    id = Column(INTEGER(11), primary_key=True)
    userid = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='user 表主键')
    ruleid = Column(String(50), nullable=False, server_default=text('0'), comment='rule表的主键，在log_type为1时才有意义。')
    username = Column(String(50), nullable=False, server_default=text('0'))
    ip = Column(String(50), nullable=False, server_default=text('0'))
    file_type = Column(TINYINT(4), nullable=False, comment='1 为题录,2 为引文')
    log_type = Column(TINYINT(4), nullable=False, server_default=text('0'), comment='1为提取，2为上传，3为移动，0为其他')
    log_json = Column(JSON, nullable=False, comment='json格式的日志信息，需与 log_type 配合使用')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict

    def get_file_type_string(self):
        files_type_dicts = {1: "meta", 2: "ref"}
        return files_type_dicts[self.file_type]

    def set_file_type_int(self, types):
        files_type_dicts = {"meta": 1, "ref": 2}
        self.file_type = files_type_dicts[types]


class User(db.Model):
    __tablename__ = 'user'

    userid = Column(INTEGER(11), primary_key=True)
    username = Column(String(50), nullable=False, unique=True, server_default=text(''))
    password = Column(String(255), nullable=False, server_default=text(''))
    ip = Column(String(50), nullable=False, server_default=text(''))
    is_acitve = Column(TINYINT(4), nullable=False, server_default=text('1'), comment='1为活用户，0为死用户')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def hash_password(self, password):
        self.password = generate_password_hash(password)
        # self.password = pwd_context.encrypt(password)

    def verify_password(self, password):
        return check_password_hash(self.password, password)
        # return pwd_context.verify(password, self.password)

    def generate_auth_token(self, expiration=60 * 60 * 24):
        s = Serializer(os.getenv('SECRET_KEY'), expires_in=expiration)
        return s.dumps({'username': self.username, 'userid': self.userid}).decode('ascii')

    @staticmethod
    def verify_auth_token(token):
        s = Serializer(os.getenv('SECRET_KEY'))
        try:
            data = s.loads(token)
        except SignatureExpired:
            return None  # 无效令牌,已过期
        except BadSignature:
            return None  # 无效令牌
        except:
            return None  # 其他错误
        user = User.query.get(data['userid'])
        return user

    def is_admin(self, username):
        if self.username == username:
            return True
        else:
            return False

    def passwd_rule(self, passwd):
        return any(char.isdigit() for char in passwd) and any(char.isalpha() for char in passwd) and any(
            char for char in passwd if
            char in ['!', '@', '#', '$', '%', '^', '*', '(', ')', '~', '`', '{', '}', '[', ']', '|', '&', '_'])

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class UpdateMeta(db.Model):
    __tablename__ = 'update_meta'

    id = Column(INTEGER(11), primary_key=True, comment='自增ID，无需人工填写')
    sub_db_id = Column(String(50), nullable=False, server_default=text(''))
    count_submit = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='提交量')
    count_update = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='更新量')
    count_create = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='新增量')
    count_deprecated = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='弃用量')
    count_delete = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='物理删除量')
    count_meta = Column(INTEGER(11), nullable=False, server_default=text('0'), comment='更新后总量')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class UpdateRef(db.Model):
    __tablename__ = 'update_ref'

    id = Column(INTEGER(11), primary_key=True, comment='自增ID，无需人工填写')
    sub_db_id = Column(String(50), nullable=False, server_default=text(''))
    count_submit = Column(String(50), nullable=False, server_default=text('0'), comment='提交量@文章量')
    count_update = Column(String(50), nullable=False, server_default=text('0'), comment='更新量@文章量')
    count_create = Column(String(50), nullable=False, server_default=text('0'), comment='新增量@文章量')
    count_deprecated = Column(String(50), nullable=False, server_default=text('0'), comment='弃用量@文章量')
    count_delete = Column(String(50), nullable=False, server_default=text('0'), comment='物理删除量@文章量')
    count_ref = Column(String(50), nullable=False, server_default=text('0'), comment='更新后总量@文章量')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class Rule(db.Model):
    __tablename__ = 'rule'
    # __table_args__ = (
    #     PrimaryKeyConstraint('username'),
    # )
    id = Column(INTEGER(11), primary_key=True)
    userid = Column(INTEGER(11), nullable=False, server_default=text("0"), comment='用户ID')
    username = Column(String(50), nullable=False, server_default=text(""), comment='用户名')
    is_deprecated = Column(TINYINT(4), nullable=False, server_default=text("0"), comment='规则是否弃用，1弃用，0未弃用。针对常规提取有效。')
    rule = Column(JSON, nullable=False)
    stat = Column(String(50), nullable=False, server_default=text(''),
                  comment='已提交: SUBMITTED; 运行中: RUNNING; 已完成: FINISHED; 已失败: FAILED; 已被杀: KILLED。')
    rule_type = Column(TINYINT(4), nullable=False, server_default=text("0"), comment='0为单次提取规则，1为常规提取规则')
    oozie_id = Column(String(50), server_default=text(""), comment='单次提取的id,后面会通过这个id查看状态')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class CountSubDbYearRef(db.Model):
    __tablename__ = 'count_subdb_year_ref'

    id = Column(INTEGER(11), primary_key=True)
    sub_db_id = Column(String(50), nullable=False, server_default=text('0'))
    count_json = Column(JSON, nullable=False)
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class CountSubdbYearMeta(db.Model):
    __tablename__ = 'count_subdb_year_meta'

    id = Column(INTEGER(11), primary_key=True)
    sub_db_id = Column(String(50), nullable=False, server_default=text('0'))
    count_json = Column(JSON, nullable=False)
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class SearchField(db.Model):
    __tablename__ = 'search_field'

    index_field = Column(String(50), primary_key=True, server_default=text(''), comment='elasticsearch中的索引字段')
    index_type = Column(TINYINT(4), nullable=False, server_default=text('0'),
                        comment='0为只有keyword索引，1为只有分词索引（text），2为同时有keyword索引和分词索引')
    field_name = Column(String(50), nullable=False, server_default=text(''))
    web_field_name = Column(String(50), nullable=False, server_default=text(''), comment='前端检索展示的字段名')
    web_serial = Column(TINYINT(4), nullable=False, server_default=text('0'), comment='web顺序号，控制前端显示顺序')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict

    @classmethod
    def get_list(cls):
        list_temp = []
        searchFieldAll = cls.all()
        for searchField in searchFieldAll:
            list_temp.append(searchField.web_field_name)
        return list_temp


class SourceType(db.Model):
    __tablename__ = 'source_type'

    type_id = Column(INTEGER(11), primary_key=True, server_default=text('-1'), comment='文献类型ID')
    type_name = Column(String(50), nullable=False, server_default=text('-1'),
                       comment='1：图书；2：期刊；3：期刊文献；4：学位论文；5：标准；6：会议；7：专利；8：法律法规；9：成果；10：多媒体；11：报纸；12：科技报告；13：产品样本；14：资讯')
    type_name_alias = Column(String(255), nullable=False, comment='类型的别名，用于兼容209的命名')
    web_serial = Column(TINYINT(4), nullable=False, server_default=text('0'), comment='web顺序号，控制前端显示顺序')
    is_active = Column(TINYINT(4), nullable=False, server_default=text('0'), comment='0为不活跃，1为活跃')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class SubDb(db.Model):
    __tablename__ = 'sub_db'
    __table_args__ = (
        Index('product_sub_db', 'product', 'sub_db', unique=True),
    )

    sub_db_id = Column(String(11), primary_key=True, comment='子库ID')
    is_active = Column(TINYINT(4), nullable=False, server_default=text('0'), comment='1为活跃，0为不活跃')
    product = Column(String(50), nullable=False, server_default=text('0'))
    sub_db = Column(String(50), nullable=False, server_default=text('0'))
    sub_db_name = Column(String(100), nullable=False, server_default=text(''))
    zt_provider = Column(String(50), nullable=False, server_default=text('0'), comment='智图provider')
    provider = Column(String(50), nullable=False, server_default=text('0'), comment='厂商')
    url = Column(String(200), nullable=False, server_default=text('0'), comment='子库url')
    summary = Column(String(255), nullable=False, server_default=text('0'), comment='子库的简介')
    source_type = Column(TINYINT(4), nullable=False, server_default=text('0'),
                         comment='文献资源类型标识。（1：图书；2：期刊；3：期刊文献；4：学位论文；5：标准；6：会议；7：专利；8：法律法规；9：成果；10：多媒体；11：报纸；12：科技报告；13：产品样本；14：资讯）。')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class GatherField(db.Model):
    __tablename__ = 'gather_field'
    __bind_key__ = 'feild209'

    doc_type = Column(String(50), primary_key=True, nullable=False, server_default=text(''), comment='文档类型：期刊、图书等')
    field_name = Column(String(50), primary_key=True, nullable=False, server_default=text(''), comment='字段名')
    field_intro = Column(String(10000), nullable=False, server_default=text('-1'), comment='字段说明')
    field_sample = Column(String(10000), nullable=False, server_default=text('-1'), comment='字段样例')

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict


class GatherFields(db.Model):
    __tablename__ = 'gather_fields'

    field_id = Column(INTEGER(11), primary_key=True, comment='主键 自增')
    field_name = Column(String(50), nullable=False, unique=True, server_default=text("''"), comment='字段名')
    field_remarks = Column(TEXT, nullable=False, comment='字段说明')
    field_type = Column(String(50), nullable=False, comment='字段类型')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"), comment='创建时间')
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), comment='更新时间')

    @classmethod
    def get_table_name(cls):
        return cls.__tablename__

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        dict["update_time"] = str(dict["update_time"])
        dict["create_time"] = str(dict["create_time"])
        return dict


class GatherFieldLog(db.Model):
    __tablename__ = 'gather_field_log'

    log_id = Column(INTEGER(11), primary_key=True, comment='主id 自增')
    username = Column(String(50), nullable=False, comment='修改人')
    table_name = Column(String(100), nullable=False, comment='修改了哪张表')
    table_id = Column(INTEGER(11), nullable=False, comment='更改的哪个id')
    types = Column(String(255), nullable=False, comment='类型 增删改查')
    before = Column(TEXT, nullable=False, comment='修改前的数据')
    after = Column(TEXT, nullable=False, comment='修改后的数据')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"), comment='创建时间')
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"), comment='更新时间')

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        dict["update_time"] = str(dict["update_time"])
        dict["create_time"] = str(dict["create_time"])
        return dict


class GatherFieldRelation(db.Model):
    __tablename__ = 'gather_field_relation'
    __table_args__ = (
        Index('source_type_id', 'type_id', 'field_id','order', unique=True),
    )

    relation_id = Column(INTEGER(11), primary_key=True, comment='主键 自增')
    type_id = Column(ForeignKey('source_type.type_id', ondelete='RESTRICT', onupdate='CASCADE'), nullable=False, comment='资源类型(source_type的外键)')
    field_id = Column(ForeignKey('gather_fields.field_id', ondelete='RESTRICT', onupdate='CASCADE'), nullable=False, index=True, server_default=text("'-1'"), comment='字段id')
    index_type = Column(String(10000), nullable=False, server_default=text("'-1'"), comment='索引类型，es使用')
    field_sample = Column(String(255), nullable=False, server_default=text("'-1'"), comment='字段样例')
    order = Column(INTEGER(11), nullable=False, comment='字段排序')
    field_remarks = Column(TEXT, nullable=False, comment='字段说明')
    create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"))
    update_time = Column(TIMESTAMP, nullable=False,
                         server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))

    @classmethod
    def get_table_name(cls):
        return cls.__tablename__

    def to_json(self):
        dict = copy.deepcopy(self.__dict__)
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        dict["update_time"] = str(dict["update_time"])
        dict["create_time"] = str(dict["create_time"])
        return dict
